-- =============================================
-- Procedure: UpdateClosedAddonLoan
-- Purpose:	Update addon loan records as closed
-- Author: Dale McFarlane
-- Created: 2014-1-27
-- Modification History
-- Modified By Modification Date	Reason
-- =====================================================================================
--  Dale McFarlane    2014-2-6    Ensures that closed date cannot be after maturity date
-- =====================================================================================

-- EXEC UpdateClosedAddonLoan F03A5003, 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UpdateClosedAddonLoan]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[UpdateClosedAddonLoan]
END
GO

CREATE PROCEDURE UpdateClosedAddonLoan
@loan_no varchar(16),
@closed_date datetime,
@error_code int OUT,
@error_msg varchar(512) OUT

AS
BEGIN
	DECLARE @user_id BIGINT
	DECLARE @maturity_date DATETIME
	DECLARE @start_date DATETIME
	DECLARE @real_closed_date DATETIME
	
	
	BEGIN TRANSACTION
	
	BEGIN TRY	
		
		IF EXISTS (SELECT * FROM loan_addon WHERE loan_no = @loan_no)
		BEGIN
		
			SELECT 
				@maturity_date = end_date,
				@start_date = start_date 
			FROM loan_addon
			WHERE loan_no = @loan_no
			
			IF @maturity_date IS NOT NULL
			BEGIN
				SET @maturity_date = DATEADD(HOUR,23,@maturity_date)
				SET @maturity_date = DATEADD(MINUTE,59,@maturity_date)
				SET @maturity_date = DATEADD(SECOND,59,@maturity_date)
				PRINT 'Maturity Date: '+CAST(@maturity_date AS VARCHAR(30))
			END
			
			IF @closed_date IS NOT NULL
			BEGIN
				SET @real_closed_date = @closed_date
				SET @closed_date = DATEADD(HOUR,23,@closed_date)
				SET @closed_date = DATEADD(MINUTE,59,@closed_date)
				SET @closed_date = DATEADD(SECOND,59,@closed_date)
				PRINT 'Closed Date: '+CAST(@closed_date AS VARCHAR(30))
			END
			
			IF @maturity_date IS NOT NULL
			BEGIN
				IF @maturity_date >= @closed_date
				BEGIN
					IF @start_date <= @closed_date
					BEGIN
						UPDATE dbo.loan_addon
							SET closed_date = @real_closed_date
						WHERE loan_no = @loan_no
					END
					ELSE
					BEGIN
						SET @error_code = -3
						SET @error_msg = 'Loan cannot be closed before the disbursement date'				
					END
				END
				ELSE
				BEGIN
					SET @error_code = -1
					SET @error_msg = 'Loan cannot be closed after maturity'				
				END
			END
			ELSE
			BEGIN
				SET @error_code = -2
				SET @error_msg = 'Maturity date could not be determined'
			END
		END
		ELSE
		BEGIN
			SET @error_code = -4
			SET @error_msg = 'Loan could not be found'				
		END
	END TRY
	BEGIN CATCH
		SET @error_code = @@ERROR
		SET @error_msg = ERROR_MESSAGE()
	END CATCH
	
	IF @error_code IS NOT NULL 
	BEGIN
		ROLLBACK TRANSACTION;
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION;
	END
END
GO